package com.eascs.p2p.admin.manager.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.p2p.admin.manager.ILoaneeRefundPlanManager;
import com.eascs.p2p.common.dao.BaseDao;
import com.eascs.p2p.common.entity.LoaneeRefundPlanEntity;
import com.eascs.p2p.common.vo.LoaneeRefundPlanVO;

import jodd.util.StringUtil;

@Repository
public class LoaneeRefundPlanManagerImpl extends BaseDao implements ILoaneeRefundPlanManager {

	@Override
	public PageResult<Object[]> findPageLoaneeRefundPlanEntity(LoaneeRefundPlanVO vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getShowPage());
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT plan.id,object.bill_code,object.title, object.loanee_name,case when plan.refund_type=1 then '一次性本息' when plan.refund_type=2 then '按月付息到期还款' end as refund_type,CONCAT(plan.issue,'/',plan.total_issues) as issue,");
		sql.append(" date_format(plan.must_refund_time,'%Y-%m-%d') must_refund_time ,plan.must_refund_total,loanee.balance,case when plan.is_overdue=0 then '否' when plan.is_overdue=1 then '是' end as is_overdue");
		sql.append(" from p2p_loanee_refund_plan plan left join p2p_invest_object object on plan.object_id=object.id left join p2p_loanee loanee on loanee.id=object.loanee_id");
		sql.append("  where plan.refund_status<>1");

		//项目编号
		if (StringUtil.isNotBlank(vo.getBillCode())) {
			sql.append(" and bill_code like ?");
			sqlPageQuery.addParam("%" + vo.getBillCode() + "%");
		}
		// 标题
		if (StringUtil.isNotBlank(vo.getTitle())) {
			sql.append(" and object.title like ?");
			sqlPageQuery.addParam("%" + vo.getTitle() + "%");
		}
		
		// 借款人姓名
		if (StringUtil.isNotBlank(vo.getLoaneeName())) {
			sql.append(" and loanee_name like ?");
			sqlPageQuery.addParam("%" + vo.getLoaneeName() + "%");
		}
		
		// 状态
		if (StringUtil.isNotBlank(vo.getIsOverdue()) ) {

			sql.append(" and is_overdue = ?");
			sqlPageQuery.addParam(vo.getIsOverdue());
		}
				
		sql.append("  group by object_id order by plan.create_time desc");
	
		sqlPageQuery.setQuerySql(sql.toString());

		PageResult<Object[]> page = this.findDataWithPage(sqlPageQuery);
		return page;
	}

	@Override
	public PageResult<Object[]> findAllPageLoaneeRefundPlanEntity(LoaneeRefundPlanVO vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, 12, vo.getShowPage());
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT CONCAT(plan.issue,'/',plan.total_issues) as issue,");
		sql.append(" date_format(plan.must_refund_time,'%Y-%m-%d')  must_refund_time ,date_format(plan.actual_refund_time,'%Y-%m-%d') actual_refund_time,plan.must_refund_total,plan.must_refund_principal, plan.must_refund_interest,case when plan.is_overdue=0 then '否' when plan.is_overdue=1 then '是' end as is_overdue,");
		sql.append(" case when refund_status=0 then '未到期' when refund_status=1 then '已还款' when refund_status=2 then '未还款'  end as refund_status from p2p_loanee_refund_plan plan left join p2p_invest_object object on plan.object_id=object.id ");
		sql.append("  where  plan.object_id=? ");

		sqlPageQuery.addParam(vo.getBidId());
		sql.append("  order by plan.issue desc");
	
		sqlPageQuery.setQuerySql(sql.toString());

		PageResult<Object[]> page = this.findDataWithPage(sqlPageQuery);
		return page;
	
	}

	@Override
	public LoaneeRefundPlanVO findNeedRefundMoney(LoaneeRefundPlanVO vo) {
		StringBuilder sql = new StringBuilder("SELECT CASE WHEN plan.must_refund_time = CURDATE() THEN sum(must_refund_total) ELSE 0 END AS todayNeedMoney, CASE WHEN datediff( plan.must_refund_time, curdate()) <= 2 AND datediff( plan.must_refund_time, curdate()) >= 0 THEN sum(must_refund_total) ELSE 0 END AS threeDayNeedMoney, sum(plan.must_refund_total) AS waiteMoney FROM p2p_loanee_refund_plan plan ");
		sql.append(" left join p2p_invest_object object on plan.object_id=object.id left join p2p_loanee loanee on loanee.id=object.loanee_id WHERE refund_status <> 1") ;
		List<Object> listParams = new ArrayList<Object>();
		//项目编号
		if (StringUtil.isNotBlank(vo.getBillCode())) {
			sql.append(" and bill_code like ?");
			listParams.add("%" + vo.getBillCode() + "%");
		}
		// 标题
		if (StringUtil.isNotBlank(vo.getTitle())) {
			sql.append(" and object.title like ?");
			listParams.add("%" + vo.getTitle() + "%");
		}
		
		// 借款人姓名
		if (StringUtil.isNotBlank(vo.getLoaneeName())) {
			sql.append(" and loanee_name like ?");
			listParams.add("%" + vo.getLoaneeName() + "%");
		}
		
		// 状态
		if (StringUtil.isNotBlank(vo.getIsOverdue()) ) {

			sql.append(" and is_overdue = ?");
			listParams.add(vo.getIsOverdue());
		}
		return  this.findEntity(sql.toString(), LoaneeRefundPlanVO.class,listParams.toArray());
	}

	@Override
	public LoaneeRefundPlanVO findOverMoneyandLoanMoeny(LoaneeRefundPlanVO vo) {
		StringBuilder sql = new StringBuilder("SELECT CASE WHEN plan.refund_status = 1 THEN sum(plan.must_refund_total) ELSE 0 END AS overMoney, IFNULL(sum(must_refund_principal),0) AS loanMoney FROM p2p_loanee_refund_plan plan");
		sql.append(" left join p2p_invest_object object on plan.object_id=object.id left join p2p_loanee loanee on loanee.id=object.loanee_id WHERE 1=1") ;
		List<Object> listParams = new ArrayList<Object>();
		//项目编号
		if (StringUtil.isNotBlank(vo.getBillCode())) {
			sql.append(" and bill_code like ?");
			listParams.add("%" + vo.getBillCode() + "%");
		}
		// 标题
		if (StringUtil.isNotBlank(vo.getTitle())) {
			sql.append(" and object.title like ?");
			listParams.add("%" + vo.getTitle() + "%");
		}
		
		// 借款人姓名
		if (StringUtil.isNotBlank(vo.getLoaneeName())) {
			sql.append(" and loanee_name like ?");
			listParams.add("%" + vo.getLoaneeName() + "%");
		}
		
		// 状态
		if (StringUtil.isNotBlank(vo.getIsOverdue()) ) {

			sql.append(" and is_overdue = ?");
			listParams.add(vo.getIsOverdue());
		}
		
		return  this.findEntity(sql.toString(), LoaneeRefundPlanVO.class,listParams.toArray());
	}

	@Override
	public Integer getNeedBidNum(LoaneeRefundPlanVO vo) {
		StringBuilder sql = new StringBuilder("SELECT count(1) FROM ( SELECT plan.id FROM p2p_loanee_refund_plan plan LEFT JOIN p2p_invest_object object ON plan.object_id = object.id LEFT JOIN p2p_loanee loanee ON loanee.id = object.loanee_id WHERE plan.refund_status <> 1 ");
		List<Object> listParams = new ArrayList<Object>();
		//项目编号
		if (StringUtil.isNotBlank(vo.getBillCode())) {
			sql.append(" and bill_code like ?");
			listParams.add("%" + vo.getBillCode() + "%");
		}
		// 标题
		if (StringUtil.isNotBlank(vo.getTitle())) {
			sql.append(" and object.title like ?");
			listParams.add("%" + vo.getTitle() + "%");
		}
		
		// 借款人姓名
		if (StringUtil.isNotBlank(vo.getLoaneeName())) {
			sql.append(" and loanee_name like ?");
			listParams.add("%" + vo.getLoaneeName() + "%");
		}
		
		// 状态
		if (StringUtil.isNotBlank(vo.getIsOverdue()) ) {

			sql.append(" and is_overdue = ?");
			listParams.add(vo.getIsOverdue());
		}
	     sql.append(" GROUP BY plan.object_id    ) t ") ;
		return this.findInteger(sql.toString(),listParams.toArray());
	}

	@Override
	public LoaneeRefundPlanEntity findLoaneeRefundPlanById(String id) {
	
		return this.getById(LoaneeRefundPlanEntity.class, id);
	}

	@Override
	public boolean updateLoaneeRefundPlan(String id, int status) {
		String sql="";
		String date=DateUtils.formateStandTime(new Date());
	    if(status==0){
	    	sql="update p2p_loanee_refund_plan set refund_status=1 ,actual_refund_time=? where id=?" ;
	    }else{
	    	sql="update p2p_loanee_refund_plan set refund_status=1 ,is_overdue=1,actual_refund_time=? where id=?" ;
	    }
		return this.execute(sql, date,id);
	}
	
}
